<?php
/**
 * This class provides easy to use OO style access to MySQL database.
 * mysql extension is used so it works only with MySQL > 4.1
 * @author Arrix arrix@163.com
 */
class DbMySQL{	
	private $_server = '';
	private $_port = 3306;
	private $_userName = '';
	private $_password = '';
	private $_dbName = '';
	private $_lastAutoCommit = NULL; //holds autocommit value, used to restore the original autocommit after commitTrans
	/**
	 * Character set used for connection
	 * When set to NULL or an empty string, the default character set(commonly latin1) is used.
	 *
	 * @var string
	 */
	public $charset = 'utf8'; 
	
	public $useException = false;
	
	/**
	 * Enter description here...
	 *
	 * @var mysql
	 */
	public $_link = false; //the link resource for connection
	
	public $_result = false; //mysql_result

	private $_connected = false; //connected or not
	public $debug = false;
	public $printErrMsg = false;
	
	
	public function __construct($server, $usr, $pwd, $dbName, $port)
	{
		$this->_server = $server;
		$this->_userName = $usr;
		$this->_password = $pwd;
		$this->_dbName = $dbName;
		$this->_port = $port;
	}
	
	public function __destruct() {
		if ($this->isConnected()) {
			$this->close();
		}
	}
	
	/**
	 * Enter description here...
	 *
	 * @return unknown
	 */
	public function errorMessage() {
		$msg = mysql_error($this->_link);
		if ($msg === false || $msg === NULL) {
			$this->_halt('Error on mysql_error');
			return false;
		} else {
			return $msg;
		} 
	}

	public function errorCode() {
		$code = mysql_errno($this->_link);
		if ($code === false || $code === NULL) {
			$this->_halt('Error on mysql_errno');
			return false;
		} else {
			return $code;
		} 
	}
	public function isConnected()
	{
		return $this->_connected;
	}
	
	/**
	 * Connects to the server
	 *
	 * @param bool $autoSelectDb
	 * @return mysql An mysql object or false on error
	 */
	public function connect($autoSelectDb = true) {
		if ($this->_connected) {
			return true;
		}
		$this->_link = mysql_connect($this->_server, $this->_userName, $this->_password);
		if (!$this->_link) {
			$this->_connected = false;
			$this->_halt("Failed to connect to database server");
			return false;
		}
		$this->_connected = true;
		if (!empty($this->charset)) {
			$this->execute("set names ".$this->charset);
		}
		if($autoSelectDb) {
			if(!$this->selectdb()) {
				return false;
			}
		}
		return $this->_link;
	}
	public function changeUser($usr, $pwd, $dbName = NULL)
	{
		$this->_userName = $usr;
		$this->_password = $pwd;
		if (is_string($dbName)) {
			$this->_dbName = $dbName;
		}
		if(!mysql_change_user($usr, $pwd, $dbName,$this->_link)) {
			$this->_halt("Failed to change user");
			return false;
		}
		return true;
	}
	public function selectDb($dbName = NULL) { 
		if (is_string($dbName)) {
			$this->_dbName = $dbName;
		}
		if(!mysql_select_db($this->_dbName,$this->_link)){
			$this->_halt("Failed to select database");
			return false;
		}
		return true;
	}
	
	/**
	 * A safe wraper of mysql_query
	 *
	 */
	public function query($queryPattern, $paraArray = NULL) {
		$queryStr = $this->buildQueryString($queryPattern, $paraArray);
		return $this->_query($queryStr);
	}
	private function _query($sql) {
		if ($this->debug) {
			echo "The query text is:<br/>$sql<br/>";
		}
		$this->_result = false;
		$this->_result = mysql_query($sql,$this->_link);

		if ($this->_result === false || $this->_result === NULL) {
			$this->_halt('Query failed.<br/>'."$sql<br/>".$this->errorMessage());
		}
		return $this->_result;
	}
	
	/**
	 * @param  int $page  current page
	 * @param  int $pageSize how many records per page
	 * @return  the query result or false on error
	 */
	private function _query_paged($sql, $page, $pageSize, &$pageInfo, $countSql = NULL)
	{
		$pageInfo = $this->_getPageInfo($sql, $page, $pageSize, $countSql);
		if ($pageInfo === false) {
			$this->_halt("获取分页信息时出错");
			return false;
		} else {
			return $this->_query($pageInfo['sql']);
		}
	}
	/**
	 * @param  int $page  current page
	 * @param  int $pageSize how many records per page
	 * @return  the query result or false on error
	 */
	public function query_paged($queryPattern, $page, $pageSize, &$pageInfo, $paraArray = NULL, $countSql = NULL)
	{
		$queryStr = $this->buildQueryString($queryPattern, $paraArray);
		if ($countSql) $countSql = $this->buildQueryString($countSql, $paraArray);
		return $this->_query_paged($queryStr, $page, $pageSize, $pageInfo, $countSql);
	}
	/**
	 * @param string $sql
	 * @param  int $page  current page
	 * @param  int $pageSize how many records per page
	 * @param string $countSql A sql query to fetch total item count for pagination. It should be optimized. 
	 * @return  array  infomation about paging a or false on error
	 */
	private function _getPageInfo($sql, $page, $pageSize, $countSql=NULL)
	{
		//check parameters
		if(!is_int($page) || !is_int($pageSize))
		return false; //error
		else {
			$pageSize = (int)$pageSize;
			$page = (int)$page;
		}
		//$countSql = preg_replace('/SELECT.*?FROM/is','SELECT COUNT(*) count FROM', $sql);
		if ($countSql === NULL)
			$countSql = "SELECT COUNT(*) count FROM ($sql) a";
		$row = $this->_fetchOne($countSql, MYSQL_NUM);
		if ($row === false) {
			return false;
		}
		$n = (int)$row[0];
		$data['pageSize'] = (int)$pageSize < 1 ? 10 : (int)$pageSize;
		$data['recordCount'] = $n;
		$data['pageCount'] = ceil($data['recordCount']/$data['pageSize']);
		$data['page'] = $data['pageCount']==0? 0: ((int)$page<1? 1: (int)$page);
		$data['page'] = $data['page']>$data['pageCount']? $data['pageCount']:$data['page'];
		$data['isFirst'] = $data['page']>1? false: true;
		$data['isLast'] = $data['page']<$data['pageCount']? false: true;
		$data['start'] = ($data['page']==0)? 0: ($data['page']-1)*$data['pageSize']+1;
		$data['end'] = ($data['start']+$data['pageSize']-1);
		$data['end'] = $data['end']>$data['recordCount']? $data['recordCount']: $data['end'];
		$limitStart = $data['start']-1;
		$limitStart = $limitStart < 0 ? 0 : $limitStart;
		$data['sql'] = $sql."\nLIMIT ".$limitStart.','.$data['pageSize'];
		//$data['result'] = $this->_query($data['sql']);
		return $data;
	}
	function getPageInfo($queryPattern, $page, $pageSize, $paramArray)
	{
		$queryStr = $this->buildQueryString($queryPattern, $paramArray);
		return $this->_getPageInfo($queryStr, $page, $pageSize);	
	}
	private function _fetchOne($sql, $resultType = MYSQL_ASSOC) {
		$this->_result = $this->_query($sql);
		if (false == $this->_result){
			$this->_halt('Failed to fetch one array. Query failed.');
		}
		$record = mysql_fetch_array($this->_result, $resultType);
		return $record;
	}
	/**
	 * query and fetch the fist row of the result
	 * @return  array  an array that corresponds to the fetched row, or FALSE if there are no more rows. 
	 */
	public function fetchOne($queryPattern, $paramArray = NULL, $resultType = MYSQL_ASSOC) {
		$queryStr = $this->buildQueryString($queryPattern, $paramArray);
		return $this->_fetchOne($queryStr, $resultType);
	}
	/**
	 * Similar to .NET ExecuteScalar
	 *
	 * @param string $queryPattern
	 * @param array $paramArray
	 * @return mixed
	 */
	public function fetchScalar($queryPattern, $paramArray = NULL)
	{
		$row = $this->fetchOne($queryPattern, $paramArray, MYSQL_NUM);
		return $row ? $row[0] : $row; //$row may be false or null?
	}
	/**
	 * @access private
	 * @return  array array of all rows or false on error
	 */
	private function _fetch($sql, $resultType = MYSQL_ASSOC) {
		$this->_result = $this->_query($sql);
		if (false === $this->_result){
			$this->_halt('Failed to _fetch. Query failed.');
			return false;
		}
		$allRows = array();
		while (($row = mysql_fetch_array($this->_result, $resultType)) != false) {
			array_push($allRows, $row);
		}
		return $allRows;
	}
	/**
	 * query and fetch the all rows of the result
	 * @return  array  an array that holding all row arrays corresponds to the fetched rows, or FALSE on error
	 */
	public function fetch($queryPattern, $paramArray = NULL, $resultType = MYSQL_ASSOC) {
		$queryStr = $this->buildQueryString($queryPattern, $paramArray);
		return $this->_fetch($queryStr, $resultType);

	}
	private function _fetch_paged($sql, $page, $pageSize, &$pageInfo, $countSql = NULL, $resultType = MYSQL_ASSOC) {
		$pageInfo = $this->_getPageInfo($sql, $page, $pageSize, $countSql);
		if ($pageInfo === false) {
			$this->_halt("获取分页信息时出错");
			return false;
		} else {
			return $this->_fetch(($pageInfo['sql']), $resultType);
		}
	}
	/**
	 * Fetches rows with pagination
	 *
	 * @param string $queryPattern
	 * @param int $page The page index
	 * @param int $pageSize
	 * @param array $pageInfo by ref
	 * @param array $paramArray
	 * @param string $countSql The query pattern used to get the total row count, parameter replacement applies to $countSql
	 * @param int $resultType
	 * @return array
	 */
	public function fetch_paged($queryPattern, $page = 1, $pageSize = 20, &$pageInfo, $paramArray = NULL, $countSql = NULL, $resultType = MYSQL_ASSOC) {
		$queryStr = $this->buildQueryString($queryPattern, $paramArray);
		if ($countSql) $countSql = $this->buildQueryString($countSql, $paramArray);
		return $this->_fetch_paged($queryStr, $page, $pageSize, $pageInfo, $countSql, $resultType);
	}
	
	/**
	 * just like the ExecuteNonQuery of ADO.NET
	 * @access private
	 * @param  string $sql
	 * @return  int rows affected by the execution or false on error
	 */
	
	private function _execute($sql)
	{
		if($this->_query($sql) === false) //error
			return false;
		else 
			return $this->affectedRows();
	}
	/**
	 * just like the ExecuteNonQuery of ADO.NET
	 *
	 * @return  int rows affected by the execution or false on error
	 */
	public function execute($queryPattern, $paramArray = NULL)
	{
		$queryStr = $this->buildQueryString($queryPattern, $paramArray);
		return $this->_execute($queryStr);
	}
	/**
	 * Warning: autocommit is not set back in commitTrans!!!
	 * @return  bool true on success or false on fail
	 */
	public function startTrans()
	{	
		$re = $this->execute("begin");
		return !($re === false);
	}
	/**
	 *
	 * @return  bool true on success or false on fail
	 */
	public function commitTrans()
	{
		$re = $this->execute("commit");
		return !($re === false);
	}
	/**
	 *
	 * @return  bool true on success or false on fail
	 */
	public function rollbackTrans()
	{
		$re = $this->execute("rollback");
		return !($re === false);
	}
	/**
	 * Enter description here...
	 *
	 * @param mysql_result $queryResult
	 * @return int number of rows in the result or false on error
	 */
	function numRows($queryResult=NULL) {
		if($queryResult === NULL)
			$queryResult = $this->_result;
		
		$rows = mysql_num_rows($queryResult);
		if ($rows === NULL) {
			$this->_halt('Failed to get selected row number. Error on mysql_num_rows.');
			return false;
		}
		return $rows;
	}
	/**
	 * Enter description here...
	 *
	 * @param mysql_result $queryResult
	 * @return int number of columns in the result or false on error
	 */
	function numFields($queryResult=NULL) {
		if($queryResult === NULL)
			$queryResult = $this->_result;
		
		$fields = mysql_num_fields($queryResult);
		if ($fields === NULL) {
			$this->_halt('Failed to get field number. Error on mysql_num_fields.');
			return false;
		}
		return $fields;
	}
	function affectedRows($link = NULL) {
		if ($link === NULL) {
			$link = $this->_link;
		}
		
		$rows = mysql_affected_rows($link);
		if ($rows === NULL) {
			$this->_halt('Failed to get affected row number. Error on mysql_affected_rows.');
			return false;
		}
		return $rows;
	}

	function freeResult($queryResult=NULL) {
		if($queryResult === NULL)
			$queryResult = $this->_result;
		if (!mysql_free_result($queryResult)){
			$this->_halt("failed to free query result");
			return false;
		}
		return true;
	}

	function insertId(){
		$id = mysql_insert_id($this->_link);
		if ($id === false || $id === NULL){
			$this->_halt("Error on mysql_insert_id");
		}
		return $id;
	}

	function close() {
		//mysql_free_result($this->_result);
		if (!$this->_connected || !is_resource($this->_link)) {
			return;
		}
		if(!mysql_close($this->_link)) {
			$this->_halt('Failed to close connection');
			return false;	
		}
		$this->_connected = false;
		return true;
	}


	function _halt($msg){
		if ($this->printErrMsg) {
			//throw new Exception('db error');
			echo($msg);
		}
		else echo 'Sorry! DB error!';
		if($this->useException) {
			throw new Exception('db error');
		}
	}

	
	/**
	 * A System.Console.Write like query string builder
	 *  
	 * @param string	$queryStrPattern  the pattern string
	 * @param array $paramArr  the parameters array or a single scalar value
	 * @return  string  the query string
	 * usage:
	 * $sqlQuery = 'SELECT col1, col2 FROM tab1 WHERE col1 = {0} AND col3 = {1} LIMIT {2}';
	 * echo(buildQueryString($sqlQuery, array('username', 24.3, 20));
	 */
	public function buildQueryString($queryStrPattern, $paramArr = NULL) {
		if (!$paramArr) {
			return $queryStrPattern;
		}
		//make a scalar value acceptable
		if (!is_array($paramArr)) {
			$paramArr = array($paramArr);
		}
		if(count($paramArr) > 0){
			foreach (array_keys($paramArr) as $paramName) {
				$paramArr[$paramName] = $this->makeSafeForSql($paramArr[$paramName]);
			}
		}
		return preg_replace('/\{(\d+)\}/ei','$paramArr[\'$1\']', $queryStrPattern);
	}
	
	/**
	 * escapes a parameter value
	 *
	 * @param mix $value
	 * @param bool $quoteNumeric Whether to quoteNumeric value
	 * @return string
	 */
	public function makeSafeForSql($value, $quoteNumeric = true)
	{
		if (is_null($value)) {
			return 'NULL';
		} 
		if (is_bool($value)) {
			return ((bool)$value) ? 'TRUE' : 'FALSE';
		}
		if (is_int($value) || is_float($value)) {
			return strval($value);
		}
		if (is_numeric($value)) {
			if ($quoteNumeric) return "'".strval($value)."'";
			else return $value;
		}
		// Stripslashes
		if (get_magic_quotes_gpc()) {
			$value = stripslashes($value);
		}
		// Quote if not numeric
		$value = "'" . $this->escape_string($value) . "'";
		
		return $value;
	}
	private function escape_string ($string,$dbcon = NULL) {
		$ret = mysql_real_escape_string($string,$this->_link);
		if($ret === false || $ret === NULL) {
			$ret = addslashes($string);
		}
		return $ret;
	}
	
	/**
	 * Formats a date for MySQL queries
	 *
	 * @param mixed $date string|timestamp int|NULL
	 * @return string
	 */
	public static function mySqlDateTime($date=NULL)
	{
		if (is_string($date)) 
			$t = strtotime($date);
		else if(is_int($date)) 
			$t = $date;
		else 
			$t = time();
			
		if ($t === false) 
			return false;
		else 
			return date('Y-m-d H:i:s', $t);
	}
	/**
	 * Parses a standard date time string returned by MySQL server into php timestamp
	 *
	 * @param string $date
	 * @return int timestamp
	 */
	public static function fromMySqlDateTime($date) {
		throw new Exception('Not implemented');
	}

}
//end of class;
?>